package app.foodnote.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class FoodNotesDataSource {
	

	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] allColumns =  {MySQLiteHelper.COLUMN_ID, MySQLiteHelper.FOOD_NAME, MySQLiteHelper.ADDRESS,
			MySQLiteHelper.CITIES, MySQLiteHelper.PHONE_NUMBER, MySQLiteHelper.OTHER_NOTES, 
			MySQLiteHelper.LONGITUDE, MySQLiteHelper.LATITUDE
			};
	public FoodNotesDataSource(Context context){
		dbHelper = new MySQLiteHelper(context);
	}
	public void openToWrite() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}
	public void openToRead() throws SQLException {
		database = dbHelper.getReadableDatabase();
	}
	public void close(){
		dbHelper.close();
	}
	public void onUpgrade(int oldVersion, int newVersion){
		dbHelper.onUpgrade(database, oldVersion, newVersion);
	}
	public FoodNote addFoodNotes(FoodNote foodNote){
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.FOOD_NAME, foodNote.getFoodName());
		values.put(MySQLiteHelper.ADDRESS, foodNote.getAddress());
		values.put(MySQLiteHelper.CITIES, foodNote.getCities());
		values.put(MySQLiteHelper.PHONE_NUMBER, foodNote.getPhoneNumber());
		values.put(MySQLiteHelper.OTHER_NOTES, foodNote.getOtherNotes());
		values.put(MySQLiteHelper.LONGITUDE, foodNote.getLongitude());
		values.put(MySQLiteHelper.LATITUDE, foodNote.getLatitude());
		long insertId = database.insert(MySQLiteHelper.TABLE_FOODNOTE, null, values);
		Cursor cursor = database.query(MySQLiteHelper.TABLE_FOODNOTE, allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null);
		cursor.moveToFirst();
		FoodNote newFoodNote = cursorToFoodNote(cursor);
		cursor.close();
		return newFoodNote;
	}
	private FoodNote cursorToFoodNote(Cursor cursor) {
		FoodNote foodnote = new FoodNote();
		foodnote.setId(cursor.getLong(cursor.getColumnIndex(MySQLiteHelper.COLUMN_ID)));
		foodnote.setFoodName(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.FOOD_NAME)));
		foodnote.setAddress(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.ADDRESS)));
		foodnote.setCities(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.CITIES)));
		foodnote.setPhoneNumber(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.PHONE_NUMBER)));
		foodnote.setOtherNotes(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.OTHER_NOTES)));
		foodnote.setLongitude(cursor.getLong(cursor.getColumnIndex(MySQLiteHelper.LONGITUDE)));
		foodnote.setLatitude(cursor.getLong(cursor.getColumnIndex(MySQLiteHelper.LATITUDE)));
		return foodnote;
	}
	public void deleteFoodNote(String id){
		String[] arrId = {id};
		database.delete(MySQLiteHelper.TABLE_FOODNOTE, MySQLiteHelper.COLUMN_ID + "=?", arrId);
	}
	
	public void updateFoodNote(String id, FoodNote foodNote){
		String[] arrId = {id};
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.FOOD_NAME, foodNote.getFoodName());
		values.put(MySQLiteHelper.ADDRESS, foodNote.getAddress());
		values.put(MySQLiteHelper.CITIES, foodNote.getCities());
		values.put(MySQLiteHelper.PHONE_NUMBER, foodNote.getPhoneNumber());
		values.put(MySQLiteHelper.OTHER_NOTES, foodNote.getOtherNotes());
		
		database.update(MySQLiteHelper.TABLE_FOODNOTE, values, MySQLiteHelper.COLUMN_ID + "=?", arrId);
	}
	
	public void updateLocation(String id, FoodNote foodnote){
		String[] arrId = {id};
		ContentValues values = new ContentValues();
		
		values.put(MySQLiteHelper.LONGITUDE, foodnote.getLongitude());
		values.put(MySQLiteHelper.LATITUDE, foodnote.getLatitude());
		
		database.update(MySQLiteHelper.TABLE_FOODNOTE, values, MySQLiteHelper.COLUMN_ID + "=?", arrId);
	}
	
	public List<FoodNote> getAllFoodNotes(){
		List<FoodNote> foodnotes = new ArrayList<FoodNote>();
		Cursor cursor = database.query(MySQLiteHelper.TABLE_FOODNOTE, allColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while(!cursor.isAfterLast()){
			FoodNote foodnote = cursorToFoodNote(cursor);
			foodnotes.add(foodnote);
			cursor.moveToNext();
		}
		cursor.close();
		return foodnotes;
	}
	
	public Cursor getViewDetail(String id){
		String[] arrId = {id};
		Cursor c = database.rawQuery("SELECT * FROM foodnote WHERE _id=?", arrId);
		return c;
	}
	public Cursor getFoodNoteById(String Id){
		String[] arrayId = {Id};
		Cursor cursor = database.rawQuery("SELECT * FROM foodnote WHERE _id=?", arrayId);
		return cursor;
	}
	
	public Cursor getAllEntry(){
		Cursor cursor = database.query(MySQLiteHelper.TABLE_FOODNOTE, allColumns, null, null, null, null, null);
		if(cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public String getName(Cursor cursor){
		return (cursor.getString(1));
	}
	public String getAddress(Cursor cursor){
		return (cursor.getString(2));
	}
	public String getCity(Cursor cursor){
		return (cursor.getString(3));
	}
	public String getPhoneNumber(Cursor cursor){
		return (cursor.getString(4));
	}
	public String getOtherNote(Cursor cursor){
		return (cursor.getString(5));
	}
	public double getLongitude(Cursor cursor){
		return (cursor.getDouble(6));
	}
	public double getLatitude(Cursor cursor){
		return (cursor.getDouble(7));
	}
	
	public Cursor getSearchFood(String text){
		  Cursor cursor = database.query(MySQLiteHelper.TABLE_FOODNOTE, allColumns, MySQLiteHelper.FOOD_NAME +" like '%"+text+"%'", null, null, null, null);
			if(cursor != null){
				cursor.moveToFirst();
			}
			return cursor;
	  }
	  
	public Cursor getSearchAddr(String text){
		Cursor cursor = database.query(MySQLiteHelper.TABLE_FOODNOTE, allColumns, MySQLiteHelper.ADDRESS +" like '%"+text+"%' or "+MySQLiteHelper.CITIES+ " like '%"+text+"%'", null, null, null, null);
		if(cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	  }
	
	public Cursor getAllEntrySort(String orderBy){
		Cursor cursor = database.query(MySQLiteHelper.TABLE_FOODNOTE, allColumns, null, null, null, null, orderBy);
		if(cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public Cursor getSearchAll(String text){
		//Cursor cursor = database.query(MySQLiteHelper.TABLE_FOODNOTE, allColumns, MySQLiteHelper.ADDRESS +" like '%"+text+"%' or "+MySQLiteHelper.FOOD_NAME+" like '%"+text+"%' or "+
			//	MySQLiteHelper.CITIES+" like '%"+text+"%' or "+MySQLiteHelper.PHONE_NUMBER+" like '%"+text+"%' or "+MySQLiteHelper.OTHER_NOTES+" like '%"+text+"%'" , null, null, null, null);
		Cursor cursor = database.rawQuery("SELECT * FROM foodnote WHERE cities like '%"+text+"%'", null);
		//Log.d("Cursor", cursor.toString());
		if(cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	  }
	
	public Cursor countAll() {		
		Cursor c = database.rawQuery("select _id, foodname , count(_id) as countall from foodnote", null);		
		//Cursor c = database.rawQuery("select * from foodnote", null);
		if(c != null){
			c.moveToFirst();
		} 
		return c;		
	}
	
	public Cursor count() {		
		Cursor c = database.rawQuery("select _id, cities , count(_id) as count from foodnote group by cities", null);		
		//Cursor c = database.rawQuery("select * from foodnote", null);
		if(c != null){
			c.moveToFirst();
		} 
		return c;		
	}

}
